﻿Imports System.IO
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

Public Class OpenXMLXLSXHelper

    ''' <summary>
    ''' This function is used to export datatable to Spreadsheet XLSX format
    ''' </summary>
    ''' <param name="tbl">DataTable to be export</param>
    ''' <param name="FilePath">File destination to be export to</param>
    ''' <param name="FileName">File name </param>
    ''' <param name="IncludeHeader">Indicate whether to include Columns name as header</param>
    ''' <param name="SheetName">Sheet name</param>
    ''' <param name="StartingRowIndex">which row you want to use as the starting row</param>
    ''' <returns>true is success</returns>
    ''' <remarks></remarks>
    Public Function ExportDatatableToXLSX(ByVal tbl As DataTable, ByVal FilePath As String, ByVal FileName As String,
                                              Optional ByVal IncludeHeader As Boolean = True,
                                              Optional ByVal SheetName As String = "",
                                              Optional ByVal StartingRowIndex As Integer = 1) As Boolean
        Dim result As Boolean


        FilePath = StringFunc.AddSlash(FilePath)
        FileName = GenerateDestinationFileName(FileName)
        If Not Directory.Exists(FilePath) Then
            Directory.CreateDirectory(FilePath)
        End If


        Using SpreadsheetDoc As SpreadsheetDocument = SpreadsheetDocument.Create(FilePath & FileName, SpreadsheetDocumentType.Workbook)

            Dim WBPart As WorkbookPart = SpreadsheetDoc.AddWorkbookPart()
            WBPart.Workbook = New Workbook()

            If SheetName.Trim.Length = 0 Then
                SheetName = "Sheet1"
            End If
            Dim wSheets As Sheets = SpreadsheetDoc.WorkbookPart.Workbook.AppendChild(Of Sheets)(New Sheets())

            wSheets.Append(ExportDataTableToWorkSheet(SpreadsheetDoc, WBPart, tbl, SheetName, 1, IncludeHeader, StartingRowIndex))

            WBPart.Workbook.Save()
            result = True
        End Using

        Return result
    End Function

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="ds">Data Set to be export to</param>
    ''' <param name="FilePath">File destination to be export to</param>
    ''' <param name="FileName">File name </param>
    ''' <param name="IncludeHeader">Indicate whether to include Columns name as header</param>
    ''' <param name="StartingRowIndex">which row you want to use as the starting row</param>
    ''' <returns>true is success</returns>
    ''' <remarks></remarks>
    Public Function ExportDataSetToXLSX(ByVal ds As DataSet, ByVal FilePath As String, ByVal FileName As String,
                                               Optional ByVal IncludeHeader As Boolean = True,
                                               Optional ByVal StartingRowIndex As Integer = 1) As Boolean
        Dim result As Boolean

        FilePath = StringFunc.AddSlash(FilePath)
        FileName = GenerateDestinationFileName(FileName)

        If Not Directory.Exists(FilePath) Then
            Directory.CreateDirectory(FilePath)
        End If
        Using SpreadsheetDoc As SpreadsheetDocument = SpreadsheetDocument.Create(FilePath & FileName, SpreadsheetDocumentType.Workbook)

            Dim WBPart As WorkbookPart = SpreadsheetDoc.AddWorkbookPart()
            WBPart.Workbook = New Workbook()

            Dim SheetName As String = "Sheet1"
            Dim wSheets As Sheets = SpreadsheetDoc.WorkbookPart.Workbook.AppendChild(Of Sheets)(New Sheets())
            For i As Integer = 0 To ds.Tables.Count - 1


                Dim SheetNamePrefix As String = "Sheet"
                If ds.Tables(i).TableName.Trim.Length = 0 Then
                    SheetName = "Sheet" & i
                Else
                    SheetName = ds.Tables(i).TableName.Trim
                End If

                wSheets.Append(ExportDataTableToWorkSheet(SpreadsheetDoc, WBPart, ds.Tables(i), SheetName, i + 1, IncludeHeader, StartingRowIndex))

            Next
            WBPart.Workbook.Save()
            result = True
        End Using

        Return result
    End Function

    Private Function ExportDataTableToWorkSheet(ByVal SpreadsheetDoc As SpreadsheetDocument, ByVal WBPart As WorkbookPart, ByVal tbl As DataTable, ByVal SheetName As String, ByVal SheetID As Integer, ByVal IncludeHeader As Boolean, ByVal StartingRowIndex As Integer) As Sheet

        Dim WSPart As WorksheetPart = WBPart.AddNewPart(Of WorksheetPart)()
        WSPart.Worksheet = New Worksheet(New SheetData)

        Dim wSheet As Sheet = Create_WorkSheet(SpreadsheetDoc, WSPart, SheetName, SheetID)
        Dim d1 As SheetData = WSPart.Worksheet.GetFirstChild(Of SheetData)()
        Dim header1 As Row = New Row
        header1.RowIndex = StartingRowIndex

        If IncludeHeader Then
            For Each Cols As DataColumn In tbl.Columns
                Dim HeaderCell = CreateCellContent(tbl.Columns.IndexOf(Cols) + 1, StartingRowIndex, Cols.ColumnName)
                header1.AppendChild(HeaderCell)
            Next
            d1.AppendChild(header1)
            StartingRowIndex = StartingRowIndex + 1
        End If



        Dim contentRow1 As DataRow
        For i As Integer = 0 To tbl.Rows.Count - 1
            contentRow1 = tbl.Rows(i)
            d1.AppendChild(ConvertDataRowToContentRow(contentRow1, i + StartingRowIndex))
        Next

        Return wSheet

    End Function

    Private Shared Function GenerateDestinationFileName(ByVal FileName As String) As String
        Dim DestinationFileName As String = ""
        If FileName.EndsWith(".xlsx") Then
            DestinationFileName = DestinationFileName & FileName
        Else
            DestinationFileName = DestinationFileName & FileName & ".xlsx"
        End If

        Return DestinationFileName

    End Function

    Private Function Create_WorkSheet(ByVal SpreadsheetDoc As SpreadsheetDocument, ByVal WSPart As WorksheetPart, ByVal SheetName As String, ByVal SheetID As Integer) As Sheet

        Dim wSheet As New Sheet
        wSheet.Id = SpreadsheetDoc.WorkbookPart.GetIdOfPart(WSPart)
        wSheet.SheetId = SheetID
        wSheet.Name = SheetName
        Return wSheet
    End Function

    Private Function ConvertDataRowToContentRow(ByVal dr As DataRow, ByVal rowIndex As Integer) As Row

        Dim r As New Row()
        r.RowIndex = rowIndex
        For i As Integer = 0 To dr.Table.Columns.Count - 1
            'Dim dataCell As Cell = createTextCell(i + 1, rowIndex, dr(i))
            Dim dataCell As Cell = CreateCellContent(i + 1, rowIndex, dr(i))
            r.AppendChild(dataCell)
        Next
        Return r
    End Function

    Private Function CreateCellContent(ByVal columnIndex As Integer, ByVal rowIndex As Integer, ByVal cValue As Object) As Cell
        Dim cell As New Cell()

        Try
            cell.CellValue = New CellValue(cValue)
            Select Case cValue.GetType.ToString
                Case "System.Int32"
                    cell.DataType = CellValues.Number
                Case "System.Decimal"
                    cell.DataType = CellValues.Number
                Case "System.Boolean"
                    cell.DataType = CellValues.Boolean
                Case "System.TimeSpan"
                    cell.DataType = CellValues.Date
                Case "System.DateTime"
                    cell.DataType = CellValues.Date
                Case Else
                    cell.DataType = CellValues.String
            End Select
        Catch ex As Exception
            cell.CellValue = New CellValue("")
            cell.DataType = CellValues.Error
        End Try
        
        cell.CellReference = GenerateColumnNameByColumnID(columnIndex) & rowIndex
        Return cell
    End Function

    Private Function GenerateColumnNameByColumnID(ByVal columnIndex As Integer) As String
        Dim ColumnLetterIndexRemain As Integer = columnIndex
        Dim ColumnName As String = [String].Empty
        Dim ColumnLetterID As Integer
        While ColumnLetterIndexRemain > 0
            ColumnLetterID = (ColumnLetterIndexRemain - 1) Mod 26
            ColumnName = Convert.ToChar(65 + ColumnLetterID).ToString() & ColumnName
            ColumnLetterIndexRemain = CInt((ColumnLetterIndexRemain - ColumnLetterID) \ 26)
        End While
        Return ColumnName
    End Function
End Class
